package ca.cbc.giscommon.dao;

import ca.cbc.giscommon.entity.Story;
import ca.cbc.giscommon.entity.Zone;
import ca.cbc.giscommon.util.GeometryUtil;
import ca.cbc.giscommon.util.StringUtil;
import com.vividsolutions.jts.geom.Geometry;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Formatter;
import java.util.List;

public class StoryDAO extends EntityHome<Story> {

    @Override
    public String getFullClassName() {
        return Story.class.getCanonicalName();
    }

    @SuppressWarnings("unchecked")
    public List<Story> listByAuthorName(String authorName) {
        return getCurrentSession().createCriteria(getFullClassName())
                .add(Restrictions.ilike("authorName", authorName))
                .addOrder(Order.desc("lastUpdatedDate")).list();
    }

    public List<Story> listWithZone() {
        return this.listWithZone(null, null, null, -1l, -1l, "last_updated", false, 50, 0, false);
    }

    @SuppressWarnings("unchecked")
    public List<Story> listWithZone(String zoneIds, Geometry bbox, String categoryIds, long startTime, long endTime, String orderBy, boolean isAscending, int limit, int offset, boolean includeAll) {
        StringBuilder queryBuilder = null;
        if (!StringUtil.instance.isNullOrEmpty(categoryIds)) {
            queryBuilder = new StringBuilder("SELECT {s.*}, {z.*} FROM story s inner join category c on s.category_id = c.id and c.id in (").append(categoryIds).append(")");
        } else {
            queryBuilder = new StringBuilder("SELECT {s.*}, {z.*} FROM story s");
        }
        logger.info("****listWithZone*Param includeAll:{}", includeAll);
        if (includeAll)
            queryBuilder.append(" left outer join zone z on ST_Intersects(z.the_geom, s.the_geom)");
        else {
            queryBuilder.append(" inner join zone z on ST_Intersects(z.the_geom, s.the_geom)");
            if (!StringUtil.instance.isNullOrEmpty(zoneIds)) {
                queryBuilder.append(" and z.id in (").append(zoneIds).append(")");
            } else if (bbox != null) {
                queryBuilder.append(new Formatter().format(" and ST_Intersects(ST_GeomFromText('%s', %d), s.the_geom)", bbox.toString(), GeometryUtil.instance.getSRID()).toString());
            }
        }
        if (startTime > 0l) {
            queryBuilder.append(" and last_updated >= '").append(new Timestamp(startTime)).append("'");
        }

        if (endTime > 0l) {
            queryBuilder.append(" and last_updated <= '").append(new Timestamp(endTime)).append("'");
        }

        queryBuilder.append(new Formatter().format(" ORDER BY s.%s %s", orderBy, getAscOrDesc(isAscending)));

        if (limit > 0) {
            queryBuilder.append(" LIMIT " + limit);
        }

        if (offset > 0) {
            queryBuilder.append(" OFFSET " + offset);
        }

        List<Object> storyAndZones = getCurrentSession().createSQLQuery(queryBuilder.toString()).addEntity("s", Story.class).addEntity("z", Zone.class).list();
        List<Story> stories = new ArrayList<Story>();
        for (Object obj : storyAndZones) {
            Object[] objArray = (Object[]) obj;
            if (objArray.length != 2) {
                logger.error("Error! Something is wrong, only one zone per story {}.", Arrays.toString(objArray));
                continue;
            }

            Story story = (Story) objArray[0];
            Zone zone = (Zone) objArray[1];
            story.setZone(zone);

            stories.add(story);
        }
        logger.info("****listWithZone*results : {}", stories.size());
        return stories;
    }
}
